package top.hcode.hoj.collstat;

import cn.hutool.core.util.ArrayUtil;
import org.nutz.lang.Times;
import org.nutz.lang.util.NutMap;
import org.sagacity.sqltoy.model.MapKit;
import top.hcode.hoj.BaseDbApp;
import top.hcode.hoj.NumParseApp;
import top.hcode.hoj.common.LazyDao;
import top.hcode.hoj.utils.SqlUtil;

import java.util.Date;
import java.util.List;
import java.util.Map;

import static top.hcode.hoj.utils.SqlUtil.sql;

/**
 * :author orangej
 * :since 2025/3/4
 */
public class CollStatApp extends BaseDbApp {

    // 中学	1895783965273370625
    // 小学	1895762323952381954
    private static final String collId = "1895762323952381954";             // 小学活动id
//    private static final String collId = "1895783965273370625";             // 中学活动id
    private static final Date startTime = Times.D("2025-3-1");          // 活动开始时间
    private static final Date endTime = Times.D("2025-3-7 23:59:59");   // 活动结束时间
    private static LazyDao dao = buildLazyDao();

    public static void main(String[] args) {
        Map<String, Object> params = MapKit.startOf("collId", collId)
                .of("startDate", startTime)
                .endOf("endDate", endTime);

        // 参与人数
        int totalNum = dao.fetchInt(SqlUtil.sql("select count(1) from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate"), params);

        // 交卷人数
        int submitNum = dao.fetchInt(SqlUtil.sql("select count(1) from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate and submit_at is not null;"), params);

        // 学校数量
        int schoolNum = dao.fetchInt(SqlUtil.sql("select count(distinct t1.org_id) from (select user_id from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate) t0\n" +
                                                 "left join school_has_user t1 on t0.user_id = t1.user_id"), params);

        // 班级数量
        int classNum = dao.fetchInt(SqlUtil.sql("select count(DISTINCT class_id) from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate"), params);
        
        // 参与学校前10
        List<NutMap> top10School = dao.queryMap(SqlUtil.sql("select org_name, cnt from \n" +
                                                            "(\n" +
                                                            "select t1.org_id, count(*) cnt from \n" +
                                                            "(select user_id from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate) t0\n" +
                                                            "left join school_has_user t1 on t0.user_id = t1.user_id\n" +
                                                            "group by t1.org_id\n" +
                                                            ") t9 left join org t8 on t9.org_id = t8.id order by cnt desc limit 10"), params);

        System.out.printf("参与人数：%s\r\n", totalNum);
        System.out.printf("交卷人数：%s\r\n", submitNum);
        System.out.printf("提交率：%.2f%%\r\n", submitNum * 100.0 / totalNum);
        System.out.printf("学校数量：%s\r\n", schoolNum);
        System.out.printf("班级数量：%s\r\n", classNum);

        System.out.print("\r\n\n\n参与学校前10：\r\n");
        for (NutMap school : top10School) {
            System.out.printf("%s\t\t\t\t%s\r\n", school.getString("orgName"), school.getInt("cnt"));
        }
        
        // 得分20名
        List<NutMap> top20Score = dao.queryMap(SqlUtil.sql("select t3.org_name schoolName, t2.org_name className, real_name, objective_score, submit_at, minutes from \n" +
                                                           "(\n" +
                                                           "select user_id, objective_score, submit_at, TIMESTAMPDIFF(Minute,start_answer_at,submit_at) minutes from coll_user_stat " +
                                                           "where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate and TIMESTAMPDIFF(Minute,start_answer_at,submit_at) > 20 " +
                                                           "and class_id not in (select id from org_class where school_id = '1882603922043285505') order by objective_score desc limit 20\n" +
                                                           ") t0\n" +
                                                           "left join class_has_user t1 on t0.user_id = t1.user_id\n" +
                                                           "left join org t2 on t1.org_id = t2.id -- class\n" +
                                                           "left join org t3 on t3.tree_code = SUBSTR(t2.tree_code, 1, 4)\t\t\n" +
                                                           "left join org_user t4 on t0.user_id = t4.id;"), params);
        System.out.print("\r\n\n\n得分20名：\r\n");
        System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\r\n", "学校", "班级", "姓名", "得分", "交卷时间", "耗时");
        for (NutMap score : top20Score) {
            System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\r\n", score.getString("schoolName"), score.getString("className"), score.getString("realName"), score.getInt("objectiveScore"), score.getString("submitAt"), score.getInt("minutes"));
        }

        // 选择题得分
        List<Integer> scoreList = dao.queryInt(SqlUtil.sql("select objective_score from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate and submit_at is not null;"), params);
        System.out.println("\n\n\n选择题得分：\n");
        NumParseApp.analyze(scoreList);

        // 交卷时间
        List<Integer> timeList = dao.queryInt(SqlUtil.sql("select TIMESTAMPDIFF(Minute,start_answer_at,submit_at) from coll_user_stat where coll_id = :collId and start_answer_at > :startDate and start_answer_at < :endDate and submit_at is not null;"), params);
        System.out.println("\n\n\n交卷时间分析：\n");
        NumParseApp.analyze(timeList);
    }

}
